import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ExamDAO {

    public List<Exam> getAllExamsForStudent() {
        List<Exam> exams = new ArrayList<>();
        String sql = "SELECT * FROM exams WHERE status = 'published' ORDER BY start_time DESC";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                Exam exam = new Exam();
                exam.setId(rs.getInt("id"));
                exam.setTitle(rs.getString("title"));
                exam.setDescription(rs.getString("description"));
                exam.setTeacherId(rs.getInt("teacher_id"));
                exam.setStartTime(rs.getTimestamp("start_time"));
                exam.setEndTime(rs.getTimestamp("end_time"));
                exam.setDuration(rs.getInt("duration"));
                exam.setTotalScore(rs.getDouble("total_score"));
                exam.setStatus(rs.getString("status"));
                exams.add(exam);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return exams;
    }

    public List<Exam> getExamsByTeacher(int teacherId) {
        List<Exam> exams = new ArrayList<>();
        String sql = "SELECT * FROM exams WHERE teacher_id = ? ORDER BY created_at DESC";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, teacherId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Exam exam = new Exam();
                exam.setId(rs.getInt("id"));
                exam.setTitle(rs.getString("title"));
                exam.setDescription(rs.getString("description"));
                exam.setTeacherId(rs.getInt("teacher_id"));
                exam.setStartTime(rs.getTimestamp("start_time"));
                exam.setEndTime(rs.getTimestamp("end_time"));
                exam.setDuration(rs.getInt("duration"));
                exam.setTotalScore(rs.getDouble("total_score"));
                exam.setStatus(rs.getString("status"));
                exams.add(exam);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return exams;
    }

    public Exam getExamById(int examId) {
        String sql = "SELECT * FROM exams WHERE id = ?";
        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, examId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Exam exam = new Exam();
                exam.setId(rs.getInt("id"));
                exam.setTitle(rs.getString("title"));
                exam.setDescription(rs.getString("description"));
                exam.setTeacherId(rs.getInt("teacher_id"));
                exam.setStartTime(rs.getTimestamp("start_time"));
                exam.setEndTime(rs.getTimestamp("end_time"));
                exam.setDuration(rs.getInt("duration"));
                exam.setTotalScore(rs.getDouble("total_score"));
                exam.setPassScore(rs.getDouble("pass_score"));
                exam.setStatus(rs.getString("status"));
                exam.setAutoGrade(rs.getBoolean("auto_grade"));
                return exam;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean createExam(Exam exam) {
        String sql = "INSERT INTO exams (title, description, teacher_id, start_time, end_time, duration, total_score, pass_score, status, auto_grade) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setString(1, exam.getTitle());
            stmt.setString(2, exam.getDescription());
            stmt.setInt(3, exam.getTeacherId());
            stmt.setTimestamp(4, exam.getStartTime());
            stmt.setTimestamp(5, exam.getEndTime());
            stmt.setInt(6, exam.getDuration());
            stmt.setDouble(7, exam.getTotalScore());
            stmt.setDouble(8, exam.getPassScore());
            stmt.setString(9, exam.getStatus());
            stmt.setBoolean(10, exam.isAutoGrade());

            int result = stmt.executeUpdate();
            if (result > 0) {
                ResultSet keys = stmt.getGeneratedKeys();
                if (keys.next()) {
                    exam.setId(keys.getInt(1));
                }
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateExam(Exam exam) {
        String sql = "UPDATE exams SET title = ?, description = ?, start_time = ?, end_time = ?, duration = ?, total_score = ?, pass_score = ?, status = ? WHERE id = ?";
        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, exam.getTitle());
            stmt.setString(2, exam.getDescription());
            stmt.setTimestamp(3, exam.getStartTime());
            stmt.setTimestamp(4, exam.getEndTime());
            stmt.setInt(5, exam.getDuration());
            stmt.setDouble(6, exam.getTotalScore());
            stmt.setDouble(7, exam.getPassScore());
            stmt.setString(8, exam.getStatus());
            stmt.setInt(9, exam.getId());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 检查考试是否有人作答
     */
    public boolean hasExamRecords(int examId) {
        String sql = "SELECT COUNT(*) FROM exam_records WHERE exam_id = ?";
        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, examId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除考试（只能删除无人作答的考试）
     */
    public boolean deleteExam(int examId) {
        // 首先检查是否有人作答
        if (hasExamRecords(examId)) {
            return false; // 有人作答，不能删除
        }

        Connection conn = null;
        try {
            conn = DatabaseConnection.getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 先删除相关的题目
            String deleteQuestionsSQL = "DELETE FROM questions WHERE exam_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteQuestionsSQL)) {
                stmt.setInt(1, examId);
                stmt.executeUpdate();
            }

            // 再删除考试
            String deleteExamSQL = "DELETE FROM exams WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteExamSQL)) {
                stmt.setInt(1, examId);
                int result = stmt.executeUpdate();

                if (result > 0) {
                    conn.commit(); // 提交事务
                    return true;
                } else {
                    conn.rollback(); // 回滚事务
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // 恢复自动提交
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
}